<?php
defined ( 'BASEPATH' ) or exit ( 'No direct script access allowed' );
date_default_timezone_set ( 'prc' );
require_once (dirname ( __FILE__ ) . "/../../PHPExcel/PHPExcel.php");
class Cost extends CI_Controller {
	public function __construct() {
		parent::__construct ();
		if (! $this->session->userdata ( 'logged_in' )) {
			redirect ( 'index_admin/login' );
			exit ();
		}
	}
	/*
	 * 返回支出列表
	 */
	public function index() {
		$name = $this->uri->segment(4);
    	$name=urldecode($name);//转化为汉字
    	if(empty($name)){
    		$name="empty";
    	}
		
		$this->load->library ( 'pagination' );
		// 每页显示数据条数
		$per_page=10;//暂定为10条
		
		$config['base_url']=site_url('cost/index/name/'.$name);
		$this->load->model ( 'cost_model' );
		
		if($name=="empty"){//这里是为了搜索
    		$name=null;
    	}
		
		// 数据总数
		$config ['total_rows'] = $this->cost_model->count_costs($name);
		$config ['per_page'] = $per_page;
		$config ['first_link'] = '第一页';
		$config ['last_link'] = '最后一页';
		$config ['next_link'] = '下一页';
		$config ['prev_link'] = '上一页';
		$config['uri_segment'] =5;
		
		$this->pagination->initialize($config);
		$data['links']=$this->pagination->create_links();
		//偏移量
		$offset=intval($this->uri->segment(5));
		$data ['costs'] = $this->cost_model->find_all_costs ($name,$per_page,$offset);
		$data ['title'] = '支出列表 - 集市宝仓库管理系统';
		$this->load->view ( 'header', $data );
		$this->load->view ( 'cost/index' );
		$this->load->view ( 'footer' );
	}
	/*
	 * 跳转到添加支出页面
	 */
	public function add() {
		$data ['title'] = '添加支出 - 集市宝仓库管理系统';
		$this->load->view ( 'header', $data );
		$this->load->view ( 'cost/add' );
		$this->load->view ( 'footer' );
	}
	/*
	 * 保存支出信息
	 */
	public function savecost() {
		$name = $this->input->post('name');
		$content=explode('-',$name);
		$productid=$content[1];
		$cname=$content[0];
		$price = $this->input->post('price');
		$amount = $this->input->post('amount');
		$unit = $this->input->post('unit');
		$total = $this->input->post('total');
		if(empty($total)){
			$total=$price*$amount;
		}
		$buytime = $this->input->post('buytime');
		$note = $this->input->post('note');
		$cost = array (
			    'productid' => $productid,
				'name' => $cname,
				'price' => $price, 
				'amount' => $amount,
				'unit' => $unit, 
				'total' => $total,
				'cost_time' => $buytime,
				'note' => $note, 
		);
		$this->load->model ( 'cost_model' );
		$costid=$this->cost_model->create ( $cost );
		if(!empty($costid)){
			$this->load->model ( 'product_model' );
			if(!empty($productid)){
				$product=$this->product_model->load($productid);
				if(!empty($product['isstock'])){//更新库存
					$temp=$product['stock']+$amount;
					if($temp>0){//库存大于0修改商品状态
						$newproduct= array (
							'stock' => $temp,
						    'short' => 0
						);
					}else{
						$newproduct= array (
							'stock' => $temp
						);
					}
					$this->product_model->update($productid,$newproduct) ;
					$this->record($costid,$productid,$amount,$temp,1);//库存记录
				}
			}
			if(!empty($price)){
				$newproduct= array (
			    	'purprice' => $price
				);
				$this->product_model->update ( $productid,$newproduct );//更新进货价
			}
			return_json(TRUE,'','添加成功！'); 
		}else{
			return_json(FALSE,'','添加失败！');
		}
	}
	/*
	 * 保存其他支出信息
	 */
	public function saveothercost() {
		$name = $this->input->post('name');
		$total = $this->input->post('total');
		$time = $this->input->post('time');
		$note = $this->input->post('note');
		$cost = array (
				'name' => $name,
				'total' => $total,
				'cost_time' => $time,
				'note' => $note, 
		);
		$this->load->model ( 'cost_model' );
		$costid=$this->cost_model->create ( $cost );
		if(!empty($costid)){
			return_json(TRUE,'','添加成功！'); 
		}else{
			return_json(FALSE,'','添加失败！');
		}
	}
	/*
	 * 删除支出信息
	 */
	public function delete() {
	    $id = $this->input->post('id');
		$this->load->model ( 'cost_model' );
		$cost=$this->cost_model->load($id);
		$this->load->model ( 'product_model' );
		$productid=$cost['productid'];
		$amount=$cost['amount'];
		if(!empty($productid)){
			$product=$this->product_model->load($productid);
			if(!empty($product['isstock'])){//更新库存
				$temp=$product['stock']-$amount;
				if($temp>0){//库存小于等于0记为补货中
					$newproduct = array (
						'stock' => $temp
					);
				}else{
					$newproduct = array (
						'stock' => $temp,
						'short' => 1
					);
				}
				$this->product_model->update($productid,$newproduct);
				$this->record($id,$productid,-$amount,$temp,1);//库存记录
			}
		}
		if ($this->cost_model->delete ( $id )) {
			return_json(TRUE,'','删除成功！'); 
	    }else{
	    	return_json(FALSE,'','删除失败！');
	    }
    }
	/*
	 * 导出成本分析
	 */
	public function exportCosts() {
		$startdate = $this->uri->segment(4);
		$enddate = $this->uri->segment(6);
		$resultPHPExcel = new PHPExcel ();
		$objActSheet = $resultPHPExcel->getActiveSheet();
		$objActSheet->setTitle('支出统计');
		$i=1;
		for($start = strtotime($startdate); $start <= strtotime($enddate);$start += 3600*24){
			$this->load->model ( 'cost_model' );
			$costs = $this->cost_model->find_export_costs (date ( "Y-m-d", $start) );
			if(empty($costs)){
				continue;
			}
			$objActSheet->setCellValue ( 'A'.$i, '日期：'.date ( "Y-m-d", $start) );
			$i++;
			$j=$i;
			$objActSheet->setCellValue ( 'A'.$i, '商品名称' );
			$objActSheet->setCellValue ( 'B'.$i, '进货价' );
			$objActSheet->setCellValue ( 'C'.$i, '数量' );
			$objActSheet->setCellValue ( 'D'.$i, '总价(元)' );
			$objActSheet->setCellValue ( 'E'.$i, '备注' );
			$i++;
			$count=0;
			foreach($costs as $cost){
				$objActSheet->setCellValue ( 'A'.$i, $cost['name'] );
				if(!empty($cost['price'])){
					$objActSheet->setCellValue ( 'B'.$i, $cost['price'].'元/'.$cost['unit'] );
				}else{
					$objActSheet->setCellValue ( 'B'.$i,'');
				}
				$objActSheet->setCellValue ( 'C'.$i, $cost['amount'] );
				$objActSheet->setCellValue ( 'D'.$i, $cost['total']);
				$objActSheet->setCellValue ( 'E'.$i, $cost['note']);
				$count+=$cost['total'];
				$i++;
			}
			$objActSheet->setCellValue ( 'A'.$i, '共计：'.$count.'元');
			$objActSheet->mergeCells('A'.$i.':E'.$i);
			$styleArray = array(//加入边框
					'borders' => array(
							'allborders' => array(
									'style' => PHPExcel_Style_Border::BORDER_THIN
							),
					),
			);
			$objActSheet->getStyle('A'.$j.':E'.$i)->applyFromArray($styleArray);
			$i+=2;
		}
		// 设置列宽
		$objActSheet->getColumnDimension ( 'A' )->setWidth ( 45 );
		$objActSheet->getColumnDimension ( 'B' )->setWidth ( 10 );
		$objActSheet->getColumnDimension ( 'C' )->setWidth ( 10 );
		$objActSheet->getColumnDimension ( 'D' )->setWidth ( 10 );
		$objActSheet->getColumnDimension ( 'E' )->setWidth ( 10 );
		
		$resultPHPExcel->createSheet();
		$resultPHPExcel->setactivesheetindex(1);
		$objActSheet = $resultPHPExcel->getActiveSheet();
		$objActSheet->setTitle('收入统计');
		$i=1;
		for($start = strtotime($startdate); $start <= strtotime($enddate);$start += 3600*24){
			$this->load->model ( 'orderdetail_model' );
			$orderdetails = $this->orderdetail_model->find_export_orderdetails_by_date (date ( "Y-m-d", $start) );
			if(empty($orderdetails)){
				continue;
			}
			$objActSheet->setCellValue ( 'A'.$i, '日期：'.date ( "Y-m-d", $start) );
			$i++;
			$j=$i;
			$objActSheet->setCellValue ( 'A'.$i, '商品名称' );
			$objActSheet->setCellValue ( 'B'.$i, '价格' );
			$objActSheet->setCellValue ( 'C'.$i, '数量' );
			$objActSheet->setCellValue ( 'D'.$i, '总价(元)' );
			$i++;
			$count=0;
			foreach($orderdetails as $orderdetail){
				$objActSheet->setCellValue ( 'A'.$i, $orderdetail['name'] );
				$objActSheet->setCellValue ( 'B'.$i, $orderdetail['price'].'元/'.$orderdetail['unit'] );
				$objActSheet->setCellValue ( 'C'.$i, $orderdetail['amount'] );
				$total=round($orderdetail['price']*$orderdetail['amount'],2);//精确到2位小数位
				$objActSheet->setCellValue ( 'D'.$i, $total);
				$count+=$total;
				$i++;
			}
			$objActSheet->setCellValue ( 'A'.$i, '共计：'.$count.'元');
			$objActSheet->mergeCells('A'.$i.':D'.$i);
			$styleArray = array(//加入边框
					'borders' => array(
							'allborders' => array(
									'style' => PHPExcel_Style_Border::BORDER_THIN
							),
					),
			);
			$objActSheet->getStyle('A'.$j.':D'.$i)->applyFromArray($styleArray);
			$i+=2;
		}
		// 设置列宽
		$objActSheet->getColumnDimension ( 'A' )->setWidth ( 45 );
		$objActSheet->getColumnDimension ( 'B' )->setWidth ( 10 );
		$objActSheet->getColumnDimension ( 'C' )->setWidth ( 10 );
		$objActSheet->getColumnDimension ( 'D' )->setWidth ( 10 );
		
		$resultPHPExcel->createSheet();
		$resultPHPExcel->setactivesheetindex(2);
		$objActSheet = $resultPHPExcel->getActiveSheet();
		$objActSheet->setTitle('成本分析');
		$objActSheet->setCellValue ( 'A1', '日期：'.date ( "Y-m-d", strtotime($startdate)).'至'.date ( "Y-m-d", strtotime($enddate)) );
		$buycost=$this->cost_model->count_costs_by_date($startdate,$enddate);
		$objActSheet->setCellValue ( 'A2', '采购支出(元)：');
		$objActSheet->setCellValue ( 'B2', $buycost[0]['total']);
		$othercost=$this->cost_model->count_othercosts_by_date($startdate,$enddate);
		$objActSheet->setCellValue ( 'A3', '其他支出(元)：');
		$objActSheet->setCellValue ( 'B3', $othercost[0]['total']);
		$objActSheet->setCellValue ( 'A4', '总支出(元)：');
		$objActSheet->setCellValue ( 'B4', ($buycost[0]['total']+$othercost[0]['total']));
		$cashincome=$this->orderdetail_model->count_orderdetails_by_date_payment($startdate,$enddate,3);
		$objActSheet->setCellValue ( 'A5', '货到付款收入(元)：');
		$objActSheet->setCellValue ( 'B5', $cashincome[0]['total']);
		$income=$this->orderdetail_model->count_orderdetails_by_date($startdate,$enddate);
		$objActSheet->setCellValue ( 'A6', '其他收入(元)：');
		$objActSheet->setCellValue ( 'B6', $income[0]['total']-$cashincome[0]['total']);
		$objActSheet->setCellValue ( 'A7', '总收入(元)：');
		$objActSheet->setCellValue ( 'B7', $income[0]['total']);
		$objActSheet->setCellValue ( 'A8', '收支统计(元)：');
		$objActSheet->setCellValue ( 'B8', $income[0]['total']-$buycost[0]['total']-$othercost[0]['total']);
		//得到商品的库存
		$this->load->model ( 'cost_model' );
    	$totalproducts = $this->cost_model->get_products_by_date ($startdate,$enddate);
		$this->load->model ( 'orderdetail_model' );
		$products = $this->orderdetail_model->get_orderdetails_by_date ($startdate,$enddate);
		$this->load->model ( 'lost_model' );
    	$lostproducts = $this->lost_model->get_products_by_date ($startdate,$enddate);
		foreach($products as $product){
			foreach($totalproducts as  $key=>$totalproduct){
				if($product['productid']==$totalproduct['productid']){
					$totalproducts[$key]['amount']=$totalproduct['amount']-$product['amount'];
					break;
				}
			}
		}
		foreach($lostproducts as $product){//减去损耗的商品
			foreach($totalproducts as  $key=>$totalproduct){
				if($product['productid']==$totalproduct['productid']){
					$totalproducts[$key]['amount']=$totalproduct['amount']-$product['amount'];
					break;
				}
			}
		}
		$remain=0;
		$this->load->model ( 'product_model' );
		foreach($totalproducts as $totalproduct){
			if($totalproduct['amount']>0){
				if(!empty($totalproduct['productid'])){//商品ID不为空
					$tempproduct=$this->product_model->load ($totalproduct['productid']);
					if(!empty($tempproduct)){//库存中的商品被删了就不处理
						$remain+=$tempproduct['price']*$totalproduct['amount'];
					}
				}
			}
		}
		$costremain=0;
		$this->load->model ( 'product_model' );
		foreach($totalproducts as $totalproduct){
			if($totalproduct['amount']>0){
				if(!empty($totalproduct['productid'])){//商品ID不为空
					$costremain+=$totalproduct['price']*$totalproduct['amount'];
				}
			}
		}
	    $objActSheet->setCellValue ( 'A9', '库存销售价统计(元)：');
		$objActSheet->setCellValue ( 'B9', $remain);
		$objActSheet->setCellValue ( 'A10', '库存成本价统计(元)：');
		$objActSheet->setCellValue ( 'B10', $costremain);
		$objActSheet->getColumnDimension ( 'A' )->setWidth ( 20 );
		$objActSheet->getColumnDimension ( 'B' )->setWidth ( 10 );
		
		$outputFileName = '成本分析表.xls';
		$xlsWriter = new PHPExcel_Writer_Excel5 ( $resultPHPExcel );
		// ob_start(); ob_flush();
		header ( "Content-Type: application/force-download" );
		header ( "Content-Type: application/octet-stream" );
		header ( "Content-Type: application/download" );
		header ( 'Content-Disposition:inline;filename="' . $outputFileName . '"' );
		header ( "Content-Transfer-Encoding: binary" );
		header ( "Expires: Mon, 26 Jul 1997 05:00:00 GMT" );
		header ( "Last-Modified: " . gmdate ( "D, d M Y H:i:s" ) . " GMT" );
		header ( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
		header ( "Pragma: no-cache" );
		$xlsWriter->save ( "php://output" );
	}
	/*
	 * 库存损耗列表
	 */
	public function lostindex() {
		$name = $this->uri->segment(4);
    	$name=urldecode($name);//转化为汉字
    	if(empty($name)){
    		$name="empty";
    	}
		$this->load->library ( 'pagination' );
		// 每页显示数据条数
		$per_page=10;//暂定为10条
		
		$config['base_url']=site_url('cost/lostindex/name/'.$name);
		$this->load->model ( 'lost_model' );
		
		if($name=="empty"){//这里是为了搜索
    		$name=null;
    	}
		
		// 数据总数
		$config ['total_rows'] = $this->lost_model->count_losts($name);
		$config ['per_page'] = $per_page;
		$config ['first_link'] = '第一页';
		$config ['last_link'] = '最后一页';
		$config ['next_link'] = '下一页';
		$config ['prev_link'] = '上一页';
		$config['uri_segment'] =5;
		
		$this->pagination->initialize($config);
		$data['links']=$this->pagination->create_links();
		//偏移量
		$offset=intval($this->uri->segment(5));
		$data ['losts'] = $this->lost_model->find_all_losts ($name,$per_page,$offset);
		$data ['title'] = '库存损耗 - 集市宝仓库管理系统';
		$this->load->view ( 'header', $data );
		$this->load->view ( 'cost/lostindex' );
		$this->load->view ( 'footer' );
	}
	/*
	 * 返回添加库存损耗页面
	 */
	public function addlost() {
		$data ['title'] = '添加库存损耗 - 集市宝仓库管理系统';
		$this->load->view ( 'header', $data );
		$this->load->view ( 'cost/addlost' );
		$this->load->view ( 'footer' );
	}
	/*
	 * 保存库存损耗信息
	 */
	public function savelost() {
		$name = $this->input->post('name');
		$content=explode('-',$name);
		$productid=$content[1];
		$amount = $this->input->post('amount');
		$total = $this->input->post('total');
		$type = $this->input->post('type');
		$date = $this->input->post('date');
		$note = $this->input->post('note');
		$lost = array (
			    'productid' => $productid,
				'amount' => $amount,
				'total' => $total,
				'type' => $type,
				'lost_time' => $date,
				'note' => $note
		);
		$this->load->model ( 'lost_model' );
		$lostid=$this->lost_model->create ( $lost );
		if(!empty($lostid)){
			$this->load->model ( 'product_model' );
			if(!empty($productid)){
				$product=$this->product_model->load($productid);
				if(!empty($product['isstock'])){//更新库存
					$temp=$product['stock']-$amount;
					if($temp>0){//库存小于等于0记为补货中
						$newproduct = array (
							'stock' => $temp
						);
					}else{
						$newproduct = array (
							'stock' => $temp,
							'short' => 1
						);
					}
					$this->product_model->update($productid,$newproduct);
					$this->record($lostid,$productid,-$amount,$temp,2);//库存记录
				}
			}
			return_json(TRUE,'','添加成功！'); 
		}else{
			return_json(FALSE,'','添加失败！');
		}
	}
	/*
	 * 删除库存损耗信息
	 */
	public function deletelost() {
	    $id = $this->input->post('id');
		$this->load->model ( 'lost_model' );
		$lost=$this->lost_model->load($id);
		$this->load->model ( 'product_model' );
		$productid=$lost['productid'];
		$amount=$lost['amount'];
		if(!empty($productid)){
			$product=$this->product_model->load($productid);
			if(!empty($product['isstock'])){//更新库存
				$temp=$product['stock']+$amount;
				if($temp>0){//库存大于0修改商品状态
					$newproduct= array (
						'stock' => $temp,
						 'short' => 0
					);
				}else{
					$newproduct= array (
						'stock' => $temp
					);
				}
				$this->product_model->update($productid,$newproduct);
				$this->record($id,$productid,$amount,$temp,2);//库存记录
			}
		}
		if ($this->lost_model->delete ( $id )) {
			return_json(TRUE,'','删除成功！'); 
	    }else{
	    	return_json(FALSE,'','删除失败！');
	    }
    }
	/*
	 * 得到商品销售量
	 */
	public function salelist() {
		$this->load->model ( 'product_model' );
		$this->load->model ( 'orderdetail_model' );
		$stockproducts=$this->product_model->find_stock_products();
		foreach($stockproducts as  $key=>$stockproduct){
			$result= $this->orderdetail_model->get_all_amount_by_product ($stockproduct['id']);
			if(empty($result['amount'])){
				$stockproducts[$key]['amount']=0;
			}else{
				$stockproducts[$key]['amount']=$result['amount'];
			}
		}
		usort($stockproducts, function($a, $b) {
		if($a['amount']==$b['amount']){
			return 0;
		} else {
			return ($a['amount']>$b['amount']) ?1:-1;
		}
		});
		$data ['products'] = $stockproducts;
		$data ['title'] = '查看销售量 - 集市宝仓库管理系统';
		$this->load->view ( 'header', $data );
		$this->load->view ( 'cost/sales' );
		$this->load->view ( 'footer' );
	}
	/*
	 * 根据起始日期导出销售表
	 */
	public function exportSales() {
		$startdate = $this->uri->segment(4);
		$enddate = $this->uri->segment(6);
		$resultPHPExcel = new PHPExcel ();
		$objActSheet = $resultPHPExcel->getActiveSheet();
		$objActSheet->mergeCells('A1:H1');
		$objActSheet->setCellValue ( 'A1', '集市宝销售表');
		$outputFileName = '销售表.xls';
		
		$objActSheet->getStyle('A1')->getFont()->setBold(true);
		$objActSheet->getStyle('A1')->getFont()->setSize(25);
		$objActSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objActSheet->setCellValue ( 'A3', '日期：'.date ( "Y-m-d", strtotime($startdate)).'至'.date ( "Y-m-d", strtotime($enddate)) );
		$objActSheet->setCellValue ( 'G3', '单位：元');
		$objActSheet->setCellValue ( 'A4', '商品名称' );
		$objActSheet->mergeCells('A4:B4');
		$objActSheet->setCellValue ( 'C4', '单位' );
		$objActSheet->setCellValue ( 'D4', '数量' );
		$objActSheet->setCellValue ( 'E4', '单价' );
		$objActSheet->setCellValue ( 'F4', '金额' );
		$objActSheet->setCellValue ( 'G4', '成本' );
		$objActSheet->setCellValue ( 'H4', '毛利' );
		$this->load->model ( 'order_model' );
		$this->load->model ( 'orderdetail_model' );
		//$date=date ( "Y-m-d", strtotime($date));
		$orders = $this->order_model->find_export_order ($startdate,$enddate);
		$i = 5;
		if(!empty($orders)){
			$products = $this->orderdetail_model->find_export_orderdetail ($startdate,$enddate);
			$alltotal=0;
			$allcost=0;
			$allprofit=0;
			foreach ( $products as $product ) {
				$objActSheet->getRowDimension($i)->setRowHeight(25);
				$objActSheet->setCellValue ( 'A' . $i, $product ['name']);
				$objActSheet->mergeCells('A'.$i.':B'.$i);
				$objActSheet->setCellValue ( 'C' . $i, $product ['unit'] );
				$objActSheet->setCellValue ( 'D' . $i, $product ['amount'] );
				$objActSheet->setCellValue ( 'E' . $i, $product ['price'] );
				$total=round($product ['price']*$product ['amount'] ,2);
				$cost=round($product ['purprice']*$product ['amount'] ,2);
				$profit=$total-$cost;
				$objActSheet->getStyle('E'.$i)->getNumberFormat()->setFormatCode('0.00');
				$objActSheet->getStyle('F'.$i)->getNumberFormat()->setFormatCode('0.00');
				$objActSheet->getStyle('G'.$i)->getNumberFormat()->setFormatCode('0.00');
				$objActSheet->getStyle('H'.$i)->getNumberFormat()->setFormatCode('0.00');
				$objActSheet->setCellValue ( 'F' . $i, $total);
				$objActSheet->setCellValue ( 'G' . $i, $cost);
				$objActSheet->setCellValue ( 'H' . $i, $profit);
				$alltotal+=$total;
				$allcost+=$cost;
				$allprofit+=$profit;
				$i ++;
			}
			$objActSheet->setCellValue ( 'A' . $i, '合计：');
			$objActSheet->mergeCells('A'.$i.':B'.$i);
			$objActSheet->getStyle('F'.$i)->getNumberFormat()->setFormatCode('0.00');
			$objActSheet->getStyle('G'.$i)->getNumberFormat()->setFormatCode('0.00');
			$objActSheet->getStyle('H'.$i)->getNumberFormat()->setFormatCode('0.00');
			$objActSheet->setCellValue ( 'F' . $i, $alltotal);
			$objActSheet->setCellValue ( 'G' . $i, $allcost);
			$objActSheet->setCellValue ( 'H' . $i, $allprofit);
			$styleArray = array(//加入边框
					'borders' => array(
							'allborders' => array(
									'style' => PHPExcel_Style_Border::BORDER_THIN
							),
					),
			);
			$objActSheet->getStyle('A4:H'.$i)->applyFromArray($styleArray);
			$i +=2;
			$objActSheet->setCellValue ( 'A' . $i, '复核：');
			$objActSheet->setCellValue ( 'E' . $i, '制单人：');
			//左对齐
			$objActSheet->getStyle ( 'A3:H' . $i)->getAlignment ()->setHorizontal ( PHPExcel_Style_Alignment::HORIZONTAL_LEFT );
			//垂直居中
			$objActSheet->getStyle ( 'A1:H' .$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		}
		// 设置列宽
		$objActSheet->getColumnDimension ( 'A' )->setWidth ( 20 );
		$objActSheet->getColumnDimension ( 'B' )->setWidth ( 14 );
		$objActSheet->getColumnDimension ( 'C' )->setWidth (5);
		$objActSheet->getColumnDimension ( 'D' )->setWidth (5);
		$objActSheet->getColumnDimension ( 'E' )->setWidth (11);
		$objActSheet->getColumnDimension ( 'F' )->setWidth (11);
		$objActSheet->getColumnDimension ( 'G' )->setWidth (11);
		$objActSheet->getColumnDimension ( 'H' )->setWidth (11);
		
			$resultPHPExcel->createSheet();
			$resultPHPExcel->setactivesheetindex(1);
			$objActSheet = $resultPHPExcel->getActiveSheet();
			$objActSheet->setCellValue ( 'A1', '商品名称' );
			$objActSheet->mergeCells('A1:B1');
			$objActSheet->setCellValue ( 'C1', '单位' );
			$objActSheet->setCellValue ( 'D1', '数量' );
			$objActSheet->setCellValue ( 'E1', '单价' );
			$objActSheet->setCellValue ( 'F1', '金额' );
			$objActSheet->setCellValue ( 'G1', '成本' );
			$objActSheet->setCellValue ( 'H1', '毛利' );
			$i=2;
			$k=1;
			$cat=array("13","14","17","0");
			for($j=0;$j<4;$j++){
			$products = $this->orderdetail_model->find_export_sales ($startdate,$enddate,$cat[$j]);//部分分类
			$alltotal=0;
			$allcost=0;
			$allprofit=0;
			foreach ( $products as $product ) {
				$objActSheet->getRowDimension($i)->setRowHeight(25);
				$objActSheet->setCellValue ( 'A' . $i, $product ['name']);
				$objActSheet->mergeCells('A'.$i.':B'.$i);
				$objActSheet->setCellValue ( 'C' . $i, $product ['unit'] );
				$objActSheet->setCellValue ( 'D' . $i, $product ['amount'] );
				$objActSheet->setCellValue ( 'E' . $i, $product ['price'] );
				$total=round($product ['price']*$product ['amount'] ,2);
				$cost=round($product ['purprice']*$product ['amount'] ,2);
				$profit=$total-$cost;
				$objActSheet->getStyle('E'.$i)->getNumberFormat()->setFormatCode('0.00');
				$objActSheet->getStyle('F'.$i)->getNumberFormat()->setFormatCode('0.00');
				$objActSheet->getStyle('G'.$i)->getNumberFormat()->setFormatCode('0.00');
				$objActSheet->getStyle('H'.$i)->getNumberFormat()->setFormatCode('0.00');
				$objActSheet->setCellValue ( 'F' . $i, $total);
				$objActSheet->setCellValue ( 'G' . $i, $cost);
				$objActSheet->setCellValue ( 'H' . $i, $profit);
				$alltotal+=$total;
				$allcost+=$cost;
				$allprofit+=$profit;
				$i ++;
			}
			$objActSheet->setCellValue ( 'A' . $i, '合计：');
			$objActSheet->mergeCells('A'.$i.':B'.$i);
			$objActSheet->getStyle('F'.$i)->getNumberFormat()->setFormatCode('0.00');
			$objActSheet->getStyle('G'.$i)->getNumberFormat()->setFormatCode('0.00');
			$objActSheet->getStyle('H'.$i)->getNumberFormat()->setFormatCode('0.00');
			$objActSheet->setCellValue ( 'F' . $i, $alltotal);
			$objActSheet->setCellValue ( 'G' . $i, $allcost);
			$objActSheet->setCellValue ( 'H' . $i, $allprofit);
			$styleArray = array(//加入边框
					'borders' => array(
							'allborders' => array(
									'style' => PHPExcel_Style_Border::BORDER_THIN
							),
					),
			);
			$objActSheet->getStyle('A'.$k.':H'.$i)->applyFromArray($styleArray);
			$i +=2;
			$k=$i;
			}
			//左对齐
			$objActSheet->getStyle ( 'A1:H' . $i)->getAlignment ()->setHorizontal ( PHPExcel_Style_Alignment::HORIZONTAL_LEFT );
			//垂直居中
			$objActSheet->getStyle ( 'A1:H' .$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			
		$xlsWriter = new PHPExcel_Writer_Excel5 ( $resultPHPExcel );
		// ob_start(); ob_flush();
		header ( "Content-Type: application/force-download" );
		header ( "Content-Type: application/octet-stream" );
		header ( "Content-Type: application/download" );
		header ( 'Content-Disposition:inline;filename="' . $outputFileName . '"' );
		header ( "Content-Transfer-Encoding: binary" );
		header ( "Expires: Mon, 26 Jul 1997 05:00:00 GMT" );
		header ( "Last-Modified: " . gmdate ( "D, d M Y H:i:s" ) . " GMT" );
		header ( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
		header ( "Pragma: no-cache" );
		$xlsWriter->save ( "php://output" );
	}
	/*
	 * 记录库存修改
	 */
	private function record($orderid,$productid,$amount,$num,$type){
		$this->load->model ( 'stockrecord_model' );//库存记录
		$record = array (
			'orderid' => $orderid,
			'productid' => $productid,
			'amount' => $amount,
			'num' => $num,
			'type' => $type,
			'record_time' => date ( "Y-m-d H:i:s" )  
		);
		$this->stockrecord_model->create ( $record );
	}
}